Multi-Valued Relationship Attributes in Extended Entity Relationship Model and Their Mapping to Relational Schema

Conceptual modeling is one of the most important phases in designing database applications. The success of this design relies heavily on how clearly the real world requirements are represented in the conceptual model. To date, the Extended Entity Relationship (EER) model extended from the traditional Entity Relationship (ER) model is a widely used modeling technique during the phase of conceptual modeling. This paper identifies semantic ambiguities that are still present in the EER model leading to incorrect knowledge representation and eventually to incorrect design of relational database schema. These ambiguities are identified in case of many-to-many relationships which have their own attributes. This paper shows that mapping such relationships to a relational database schema generates relations having primary keys which cannot guarantee unique tuples for real world data thus violating the definition of a primary key. In addition, it shows that these relations may not satisfy second normal form. A number of such cases are elaborated and a new concept of multi-valued relationship attribute is introduced that can successfully represent these real world constraints. For this concept, a diagrammatic notation to use in ER diagram is introduced. A mapping algorithm to transform the corresponding EER model to a relational database schema is also defined. This concept of multi-valued relationship attribute and its mapping to relational schema generate relations which satisfy higher normal forms.